﻿using System;
using System.IO;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Resources;

using CE.User;
using CE.Global;
using CE.Database;
using CE.CarInsurance;

public partial class reports_ViewAgentCommision : System.Web.UI.Page
{
    private DateTime dateTimeNow = CE.Global.General.GetUTCDateTime();

    protected override void InitializeCulture()
    {
        String selectedCulture = CE.Global.General.SetCulture();
        base.InitializeCulture();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session[CE.Global.General.UserId] == null)
        {
            Response.Redirect("~/SiteUserSecureLogin.aspx");
        }

        if (!IsPostBack)
        {
            lblError.Text = "";
            lblContent.Text = "";
            ddlDay1.Visible = true;
            lblFromDate.Text = Resources.General.Date + " : ";
            lblToDate.Visible = false;
            ddlDay2.Visible = false;
            ddlMonth2.Visible = false;
            ddlYear2.Visible = false;
            ddlYear1.Items.Clear();
            ddlYear2.Items.Clear();
            LoadAgentList();
            ddlAgentList.Text = Session[CE.Global.General.UserId].ToString();

            int _cryr = dateTimeNow.Year;
            int _styr = 2010;
            for (int _year = _cryr; _year >= _styr; _year--)
            {
                ddlYear1.Items.Add(new ListItem(_year.ToString(), _year.ToString()));
                ddlYear2.Items.Add(new ListItem(_year.ToString(), _year.ToString()));
            }

            ddlDay1.Text = dateTimeNow.Day.ToString("00");
            ddlMonth1.Text = dateTimeNow.Month.ToString("00");
            ddlYear1.Text = dateTimeNow.Year.ToString("0000");
            ddlDay2.Text = dateTimeNow.Day.ToString("00");
            ddlMonth2.Text = dateTimeNow.Month.ToString("00");
            ddlYear2.Text = dateTimeNow.Year.ToString("0000");
            ddlReportType_SelectedIndexChanged(null, null);
        }

        if (Session[General.UserRole] != null && Session[General.UserRole].ToString() == "100")
        {
            ibtSubmit.Visible = false;
            ddlAgentList.Enabled = false;
            ddlReportType.Enabled = false;
            grvCommisionSummary.Visible = false;
            ddlDay1.Enabled = false;
            ddlMonth1.Enabled = false;
            ddlYear1.Enabled = false;
            lblError.Text = Resources.CarInsurance.YourAreNotAuthorizedToViewPage;
        }
    }

    protected void ibtSubmit_Click(object sender, EventArgs e)
    {
        lblError.Text = "";
        txtHidTotalAmount.Text = "0";
        lblTotalAmount.Text = "";
        lblCommision.Text = "";
        lblCommisionAmount.Text = "";
        txtReportType.Text = ddlReportType.Text;
        txtAgentId.Text = Session[CE.Global.General.UserId].ToString();
        LoadGrid();
    }

    protected void btnDownLoad_Click(object sender, EventArgs e)
    {
        GenerateExcel();
    }

    protected void ddlReportType_SelectedIndexChanged(object sender, EventArgs e)
    {
        lblError.Text = "";
        ddlDay1.Text = dateTimeNow.Day.ToString("00");
        ddlDay2.Text = dateTimeNow.Day.ToString("00");
        ddlMonth1.Text = dateTimeNow.Month.ToString("00");
        ddlMonth2.Text = dateTimeNow.Month.ToString("00");
        ddlYear1.Text = dateTimeNow.Year.ToString("0000");
        ddlYear2.Text = dateTimeNow.Year.ToString("0000");
        if (Convert.ToInt16(ddlReportType.Text) == 0)
        {
            ddlDay1.Visible = false;
            lblFromDate.Text = Resources.General.Date + " : ";
            lblToDate.Visible = false;
            ddlDay2.Visible = false;
            ddlMonth2.Visible = false;
            ddlYear2.Visible = false;
        }
        else if (Convert.ToInt16(ddlReportType.Text) == 1)
        {
            ddlDay1.Visible = true;
            lblFromDate.Text = Resources.General.Date + " : ";
            lblToDate.Visible = false;
            ddlDay2.Visible = false;
            ddlMonth2.Visible = false;
            ddlYear2.Visible = false;
        }
        else if (Convert.ToInt16(ddlReportType.Text) == 2)
        {
            lblFromDate.Text = " " + Resources.General.FromDate + " : ";
            lblToDate.Text = " " + Resources.General.ToDate + " : ";
            lblToDate.Visible = true;
            ddlDay2.Visible = true;
            ddlMonth2.Visible = true;
            ddlYear2.Visible = true;
            ddlDay1.Visible = true;
        }
    }

    protected void grvCommisionSummary_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            e.Row.Cells[6].Attributes.CssStyle.Add("text-align", "right");
            e.Row.Cells[7].Attributes.CssStyle.Add("text-align", "right");
            e.Row.Cells[8].Attributes.CssStyle.Add("text-align", "right");
            e.Row.Cells[9].Attributes.CssStyle.Add("text-align", "right");
        }
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Cells[6].Attributes.CssStyle.Add("text-align", "right");
            e.Row.Cells[7].Attributes.CssStyle.Add("text-align", "right");
            e.Row.Cells[8].Attributes.CssStyle.Add("text-align", "right");
            e.Row.Cells[9].Attributes.CssStyle.Add("text-align", "right");

            float _premium = Convert.ToSingle(DataBinder.Eval(e.Row.DataItem, "CollectedAmount"));
            float _toppremium = Convert.ToSingle(txtHidTotalAmount.Text);
            _toppremium = _toppremium + _premium;
            txtHidTotalAmount.Text = _toppremium.ToString();
            lblTotalAmount.Text = _toppremium.ToString("฿#,000.00");
        }
    }

    public bool ValidDate(Int32 Year, Int32 Month, Int32 Day)
    {
        bool _isValid = true;
        int daysInMonth = 0;
        string leap, monthValid, dayValid, yearValid;
        if (Year >= 0000)
        {
            yearValid = "yes";
        }
        else
        {
            yearValid = "no";
        }
        if (Year % 400 == 0)
        {
            leap = "yes";
        }
        else if (Year % 100 == 0)
        {
            leap = "no";
        }
        else if (Year % 4 == 0)
        {
            leap = "yes";
        }
        else
        {
            leap = "no";
        }

        switch (Month)
        {
            case 1: daysInMonth = 31;
                break;
            case 3: daysInMonth = 31;
                break;
            case 4: daysInMonth = 30;
                break;
            case 5: daysInMonth = 31;
                break;
            case 6: daysInMonth = 30;
                break;
            case 7: daysInMonth = 31;
                break;
            case 8: daysInMonth = 31;
                break;
            case 9: daysInMonth = 30;
                break;
            case 10: daysInMonth = 31;
                break;
            case 11: daysInMonth = 30;
                break;
            case 12: daysInMonth = 31;
                break;
        }

        if (Month >= 1 && Month <= 12)
        {
            monthValid = "yes";
        }
        else
        {
            monthValid = "no";
        }
        if (Month == 2)
        {
            if (leap == "no")
            {
                daysInMonth = 28;
            }
            else if (leap == "yes")
            {
                daysInMonth = 29;
            }
        }

        if (1 <= Day && Day <= daysInMonth)
        {
            dayValid = "yes";
        }

        else
        {
            dayValid = "no";
        }

        if ((dayValid == "yes") && (monthValid == "yes") && (yearValid == "yes"))
        {
            _isValid = true;
        }
        else
        {
            _isValid = false;
        }

        return _isValid;
    }

    public string GetMonth(string month)
    {
        string result = "";
        if (month == "01")
        {
            result = " Jan ";
        }
        else if (month == "02")
        {
            result = " Feb ";
        }
        else if (month == "03")
        {
            result = " Mar ";
        }
        else if (month == "04")
        {
            result = " Apr ";
        }
        else if (month == "05")
        {
            result = " May ";
        }
        else if (month == "06")
        {
            result = " Jun ";
        }
        else if (month == "07")
        {
            result = " Jul ";
        }
        else if (month == "08")
        {
            result = " Aug ";
        }
        else if (month == "09")
        {
            result = " Sep ";
        }
        else if (month == "10")
        {
            result = " Oct ";
        }
        else if (month == "11")
        {
            result = " Nov ";
        }
        else if (month == "12")
        {
            result = " Dec ";
        }
        return result;
    }

    public bool Validatioin()
    {
        lblError.Text = "";
        bool _isValid = true;
        DateTime _fromDate, _toDate;
        if (Convert.ToInt16(ddlReportType.Text) == 1)
        {
            if (ddlDay1.SelectedIndex >= 0)
            {
                if (ValidDate(Convert.ToInt32(ddlYear1.Text), Convert.ToInt32(ddlMonth1.Text), Convert.ToInt32(ddlDay1.Text)))
                {
                    _fromDate = Convert.ToDateTime(Convert.ToInt32(ddlYear1.Text) + "/" + Convert.ToInt32(ddlMonth1.Text) + "/" + Convert.ToInt32(ddlDay1.Text));
                    if (_fromDate > dateTimeNow)
                    {
                        _isValid = false;
                        lblError.Text = Resources.General.ErrDatelessorequalToday;
                    }
                }
                else
                {
                    _isValid = false;
                    lblError.Text = Resources.General.ErrSelectValidDate;
                }
            }
            else
            {
                _isValid = false;
                lblError.Text = Resources.General.ErrSelectDate;
            }
        }
        else if (Convert.ToInt16(ddlReportType.Text) == 2)
        {
            if (ddlDay1.SelectedIndex >= 0 && ddlDay2.SelectedIndex >= 0)
            {
                if (ValidDate(Convert.ToInt32(ddlYear1.Text), Convert.ToInt32(ddlMonth1.Text), Convert.ToInt32(ddlDay1.Text)))
                {
                    _fromDate = Convert.ToDateTime(Convert.ToInt32(ddlYear1.Text) + "/" + Convert.ToInt32(ddlMonth1.Text) + "/" + Convert.ToInt32(ddlDay1.Text));
                    if (ValidDate(Convert.ToInt32(ddlYear2.Text), Convert.ToInt32(ddlMonth2.Text), Convert.ToInt32(ddlDay2.Text)))
                    {
                        _toDate = Convert.ToDateTime(Convert.ToInt32(ddlYear2.Text) + "/" + Convert.ToInt32(ddlMonth2.Text) + "/" + Convert.ToInt32(ddlDay2.Text));
                        if (_fromDate <= dateTimeNow)
                        {
                            if (_fromDate > _toDate)
                            {
                                _isValid = false;
                                lblError.Text = Resources.General.ErrFromdatelessthanTodate;
                            }
                        }
                        else
                        {
                            _isValid = false;
                            lblError.Text = Resources.General.ErrFromdatelessthanToday;
                        }
                    }
                    else
                    {
                        _isValid = false;
                        lblError.Text = Resources.General.ErrSelectValidTodate;
                    }
                }
                else
                {
                    _isValid = false;
                    lblError.Text = Resources.General.ErrSelectValidFromdate;
                }
            }
            else
            {
                _isValid = false;
                lblError.Text = Resources.General.ErrSelectFromandTodate;
            }
        }
        return _isValid;
    }

    public void LoadGrid()
    {
        lblError.Text = "";
        txtHidTotalAmount.Text = "0";
        lblTotalAmount.Text = "";
        lblCommision.Text = "";
        lblCommisionAmount.Text = "";
        lblTotalAmountTitle.Text = Resources.Proposal.TotalPremium + " : ";
        lblCommisionTitle.Text = Resources.Proposal.CommissionPer + " : ";
        lblCommisionAmountTitle.Text = Resources.Proposal.TotalCommision + " : ";
        divCommision.Visible = false;
        if (Validatioin() == true)
        {
            txtReportDate1.Text = "";
            txtReportDate2.Text = dateTimeNow.Year.ToString("0000") + dateTimeNow.Month.ToString("00") + dateTimeNow.Day.ToString("00");
            txtReportDate1.Text = ddlYear1.Text;
            txtReportDate1.Text += ddlMonth1.Text;
            if (ddlReportType.SelectedIndex > 0)
            {
                if (ddlDay1.SelectedIndex >= 0)
                {
                    txtReportDate1.Text += ddlDay1.Text;
                }
            }
            if (ddlReportType.SelectedIndex == 2)
            {
                txtReportDate2.Text = ddlYear2.Text;
                txtReportDate2.Text += ddlMonth2.Text;
                txtReportDate2.Text += ddlDay2.Text;
            }
            DataTable _dtSummary = new DataTable();
            Int16 _ReportType = Convert.ToInt16(ddlReportType.Text);
            string _ReportDate1 = txtReportDate1.Text.Trim();
            string _ReportDate2 = txtReportDate2.Text.Trim();
            Int64 _AgentId = Convert.ToInt64(ddlAgentList.Text);
            if (_ReportDate1.Length == 6)
            {
                txtReportDate.Text = _ReportDate1.ToString() + "01";
            }
            else if (_ReportDate1.Length == 8)
            {
                txtReportDate.Text = _ReportDate1.ToString();
            }

            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocCXCRMCompletedApplicationSelectedList]";
            SortedList _sPara = new SortedList();

            _sPara.Add("@ReportType", _ReportType);
            _sPara.Add("@ReportDate", _ReportDate1);
            _sPara.Add("@AgentId", _AgentId);

            _dtSummary = _db.GetDataTableBySP(_spName, _sPara);
            if (_dtSummary != null && _dtSummary.Rows.Count > 0)
            {
                lblError.Text = "";
                grvCommisionSummary.DataSource = _dtSummary;
                grvCommisionSummary.DataBind();
                EnablePrintView();
                float TotalPremium = Convert.ToSingle(txtHidTotalAmount.Text);
            }
            else
            {
                grvCommisionSummary.DataSource = null;
                grvCommisionSummary.DataBind();
                EnablePrintView();
                txtHidTotalAmount.Text = "0";
                lblTotalAmount.Text = "";
                lblCommision.Text = "";
                lblCommisionAmount.Text = "";
                divCommision.Visible = false;
            }
        }
        else
        {
            grvCommisionSummary.DataSource = null;
            grvCommisionSummary.DataBind();
            EnablePrintView();
            txtHidTotalAmount.Text = "0";
            lblTotalAmount.Text = "";
            lblCommision.Text = "";
            lblCommisionAmount.Text = "";
            divCommision.Visible = false;
        }
    }

    private void EnablePrintView()
    {
        if (grvCommisionSummary.Rows.Count > 0)
        {
            btnPrint.Visible = true;
            btnDownLoad.Visible = true;
            lblRecordCount.Text = "(" + grvCommisionSummary.Rows.Count.ToString() + ") record(s). Date/Time: " + dateTimeNow.ToString("dd-MMM-yyyy hh:mm tt") + "&nbsp;&nbsp;";
        }
        else
        {
            btnPrint.Visible = false;
            btnDownLoad.Visible = false;
            lblRecordCount.Text = "No record found. Date/Time: " + dateTimeNow.ToString("dd-MMM-yyyy hh:mm tt") + "&nbsp;&nbsp;";
        }
    }

    public void GenerateExcel()
    {
        string _html = "";




        string _fileName = "AgentCommision@" + dateTimeNow.Day.ToString("00") + "-" + dateTimeNow.Month.ToString("00") + "-" + dateTimeNow.Year;
        lblContent.Text = "";
        lblError.Text = "";


        Int32 CommissionCount = 0;
        Int32 TotalFileCount = 0;
        DataSet _dsSummary = new DataSet();
        DataTable _dtSummary = new DataTable();
        Int16 _ReportType = Convert.ToInt16(ddlReportType.Text);
        string _ReportDate = txtReportDate.Text.Trim();
        Int64 AgentId = Convert.ToInt64(ddlAgentList.Text);

        MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
        string _spName = "[sprocCXCRMCompletedApplicationSelectedList]";
        SortedList _sPara = new SortedList();

        _sPara.Add("@ReportType", _ReportType);
        _sPara.Add("@ReportDate", _ReportDate);
        _sPara.Add("@AgentId", AgentId);

        _dsSummary = _db.GetDataSet(_spName, _sPara);
        if (_dsSummary != null && _dsSummary.Tables.Count > 0 && _dsSummary.Tables[0].Rows.Count > 0)
        {
            TotalFileCount += _dsSummary.Tables[0].Rows.Count;
        }
        if (TotalFileCount > 0)
        {
            string[] ApplicationDate = new string[TotalFileCount];
            string[] QuoteRefNo = new string[TotalFileCount];
            string[] AgentName = new string[TotalFileCount];
            string[] CustomerName = new string[TotalFileCount];
            string[] InsuranceType = new string[TotalFileCount];
            string[] CompanyName = new string[TotalFileCount];
            string[] VMI = new string[TotalFileCount];
            string[] CMI = new string[TotalFileCount];
            string[] Premium = new string[TotalFileCount];
            string[] CollectedAmount = new string[TotalFileCount];
            float TotalCollectedPremium = 0.0f;
            string[] Agent = new string[0];
            string[] CollectedPremium = new string[0];
            string[] Percentage = new string[0];
            string[] AgentCommission = new string[0];
            float CollectedPremiumTotal = 0.0f;
            float AgentCommissionTotal = 0.0f;


            string SCName, SCBank, CardNo, CardType, CardExpiry, Status, Commision, TotalCommision, PaymentType;
            SCName = ""; SCBank = ""; CardNo = ""; CardType = ""; CardExpiry = ""; Status = ""; Commision = ""; TotalCommision = "";
            PaymentType = "";
            for (int i = 0; i < TotalFileCount; i++)
            {
                ApplicationDate[i] = Convert.ToDateTime(_dsSummary.Tables[0].Rows[i]["ApplicationDate"].ToString()).ToString("dd/MM/yyyy hh:mm tt");
                QuoteRefNo[i] = _dsSummary.Tables[0].Rows[i]["QuoteRefNo"].ToString();
                AgentName[i] = _dsSummary.Tables[0].Rows[i]["AgentName"].ToString();
                CustomerName[i] = _dsSummary.Tables[0].Rows[i]["CustomerName"].ToString();
                InsuranceType[i] = _dsSummary.Tables[0].Rows[i]["InsuranceType"].ToString();
                CompanyName[i] = _dsSummary.Tables[0].Rows[i]["CompanyName"].ToString();
                VMI[i] = _dsSummary.Tables[0].Rows[i]["VMI"].ToString();
                CMI[i] = _dsSummary.Tables[0].Rows[i]["CMI"].ToString();
                Premium[i] = _dsSummary.Tables[0].Rows[i]["TotalPremium"].ToString();
                CollectedAmount[i] = _dsSummary.Tables[0].Rows[i]["CollectedAmount"].ToString();
                TotalCollectedPremium += Convert.ToSingle(CollectedAmount[i].ToString());
            }
            if (_dsSummary != null && _dsSummary.Tables.Count > 1 && _dsSummary.Tables[1].Rows.Count > 0)
            {
                CommissionCount = _dsSummary.Tables[1].Rows.Count;
                Agent = new string[CommissionCount];
                CollectedPremium = new string[CommissionCount];
                Percentage = new string[CommissionCount];
                AgentCommission = new string[CommissionCount];
                for (int a = 0; a < CommissionCount; a++)
                {
                    Agent[a] = _dsSummary.Tables[1].Rows[a]["AgentName"].ToString();
                    CollectedPremium[a] = _dsSummary.Tables[1].Rows[a]["CollectedAmount"].ToString();
                    if (CollectedPremium[a] != null && CollectedPremium[a].ToString() != string.Empty && CollectedPremium[a].ToString() != "0.00" && CollectedPremium[a].ToString() != "0")
                    {
                        DataTable _dtPer = new DataTable();
                        float _AgentPremium = 0.0f;
                        _AgentPremium = Convert.ToSingle(_dsSummary.Tables[1].Rows[a]["CollectedAmount"].ToString());
                        if (_AgentPremium >= 550000)
                        {
                            _dtPer = GetPercentage("550000", txtReportDate.Text.Trim());
                            if (_dtPer != null && _dtPer.Rows.Count > 0)
                            {
                                Commision = _dtPer.Rows[0]["Commission"].ToString();
                            }
                            else
                            {
                                Commision = "0.0";
                            }
                        }
                        else if (_AgentPremium >= 500000 && _AgentPremium < 550000)
                        {
                            _dtPer = GetPercentage("500000", txtReportDate.Text.Trim());
                            if (_dtPer != null && _dtPer.Rows.Count > 0)
                            {
                                Commision = _dtPer.Rows[0]["Commission"].ToString();
                            }
                            else
                            {
                                Commision = "0.0";
                            }
                        }
                        else if (_AgentPremium >= 450000 && _AgentPremium < 500000)
                        {
                            _dtPer = GetPercentage("450000", txtReportDate.Text.Trim());
                            if (_dtPer != null && _dtPer.Rows.Count > 0)
                            {
                                Commision = _dtPer.Rows[0]["Commission"].ToString();
                            }
                            else
                            {
                                Commision = "0.0";
                            }
                        }
                        else if (_AgentPremium >= 400000 && _AgentPremium < 450000)
                        {
                            _dtPer = GetPercentage("400000", txtReportDate.Text.Trim());
                            if (_dtPer != null && _dtPer.Rows.Count > 0)
                            {
                                Commision = _dtPer.Rows[0]["Commission"].ToString();
                            }
                            else
                            {
                                Commision = "0.0";
                            }
                        }
                        else if (_AgentPremium >= 300000 && _AgentPremium < 400000)
                        {
                            _dtPer = GetPercentage("300000", txtReportDate.Text.Trim());
                            if (_dtPer != null && _dtPer.Rows.Count > 0)
                            {
                                Commision = _dtPer.Rows[0]["Commission"].ToString();
                            }
                            else
                            {
                                Commision = "0.0";
                            }
                        }
                        else if (_AgentPremium < 300000)
                        {
                            _dtPer = GetPercentage("0", txtReportDate.Text.Trim());
                            if (_dtPer != null && _dtPer.Rows.Count > 0)
                            {
                                Commision = _dtPer.Rows[0]["Commission"].ToString();
                            }
                            else
                            {
                                Commision = "0.0";
                            }
                        }
                        float _comper = Convert.ToSingle(Commision.ToString()) * 100;
                        float _com = Convert.ToSingle(Commision.ToString());
                        Percentage[a] = _comper.ToString() + "%";
                        AgentCommission[a] = (_AgentPremium * _com).ToString();
                        CollectedPremiumTotal += _AgentPremium;
                        AgentCommissionTotal += (_AgentPremium * _com);
                    }
                    else
                    {
                        Percentage[a] = "0.0%";
                        AgentCommission[a] = "0.00";
                    }
                }
            }

            _html += "<html>";
            _html += "<head>";
            _html += "<meta http-equiv='content-type' content='text/html; charset=UTF-8'>";
            _html += "</head>";
            _html += "<body>";

            _html += "<table width='100%' cellpadding='1' cellspacing='1'>";
            _html += "<tr>";

            //Broker Commission Summary - Start
            _html += "<td>";
            _html += "<table width='35%' cellpadding='1' cellspacing='1' border='1'>";
            _html += "<tr><td align='left' colspan='4' style='font-size:20px; background-color:#D8D8D8;'><b>" + Resources.General.BrokerCommission + "</b></td></tr>";
            _html += "<tr>";
            _html += "<td align='center' style='font-size:14px; background-color:#cbffce;'><b>" + Resources.General.Broker + "</b></td>";
            _html += "<td align='center' style='font-size:14px; background-color:#cbffce;'><b>" + Resources.General.TotalCollectedAmount + "</b></td>";
            _html += "<td align='center' style='font-size:14px; background-color:#cbffce;'><b>" + Resources.General.ComPer + "</b></td>";
            _html += "<td align='center' style='font-size:14px; background-color:#cbffce;'><b>" + Resources.General.Commission + "</b></td>";
            _html += "</tr>";

            for (int b = 0; b < CommissionCount; b++)
            {
                _html += "<tr>";
                _html += "<td align='left'>" + Agent[b].ToString() + "</td>";
                _html += "<td align='right' style='mso-number-format:\\#\\,\\#\\#0\\.00'>" + CollectedPremium[b].ToString() + "</td>";
                _html += "<td align='center'>" + Percentage[b].ToString() + "</td>";
                _html += "<td align='right' style='mso-number-format:\\#\\,\\#\\#0\\.00'>" + AgentCommission[b].ToString() + "</td>";
                _html += "</tr>";
            }

            _html += "<tr>";
            _html += "<td align='right' style='font-size:16px; background-color:#FFE100;'><b>&nbsp;</b></td>";
            _html += "<td align='right' style='font-size:16px; background-color:#FFE100; mso-number-format:\\#\\,\\#\\#0\\.00'>" + CollectedPremiumTotal.ToString() + "</td>";
            _html += "<td align='right' style='font-size:16px; background-color:#FFE100;'><b>&nbsp;</b></td>";
            _html += "<td align='right' style='font-size:16px; background-color:#FFE100; mso-number-format:\\#\\,\\#\\#0\\.00'>" + AgentCommissionTotal.ToString() + "</td>";
            _html += "</tr>";

            _html += "</table>";
            _html += "</td>";
            //Broker Commission Summary - End

            _html += "<td><table width='10%' cellpadding='1' cellspacing='1'><tr><td>&nbsp;</td></tr></table></td>";

            //Report Details - Start
            _html += "<td>";
            _html += "<table width='35%' cellpadding='1' cellspacing='1' border='1'>";
            _html += "<tr><td align='left' colspan='4' style='font-size:20px; background-color:#D8D8D8;'><b>" + Resources.General.ReportDetails + "</b></td></tr>";
            _html += "<tr>";
            _html += "<td align='left' colspan='2' style='font-size:14px; background-color:#F5FBEF;'><b>" + Resources.General.User + "</b></td>";
            _html += "<td align='left' colspan='2' style='font-size:14px; background-color:#F5FBEF;'><b>" + Session[CE.Global.General.UserName].ToString() + "</b></td>";
            _html += "</tr>";
            _html += "<tr>";
            _html += "<td align='left' colspan='2' style='font-size:14px; background-color:#F5FBEF;'><b>" + Resources.General.SelectedMonth + "</b></td>";
            _html += "<td align='left' colspan='2' style='font-size:14px; background-color:#F5FBEF; mso-number-format:mmm\\,yyyy'><b>" + GetReportMonth(txtReportDate.Text.Trim()) + "</b></td>";
            _html += "</tr>";
            _html += "<tr>";
            _html += "<td align='left' colspan='2' style='font-size:14px; background-color:#F5FBEF;'><b>" + Resources.General.ReportDateTime + "</b></td>";
            _html += "<td align='left' colspan='2' style='font-size:14px; background-color:#F5FBEF; mso-number-format:dd\\-mmm\\-yyyy hh\\:mm tt'><b>" + dateTimeNow + "</b></td>";
            _html += "</tr>";
            _html += "</table>";
            _html += "</td>";
            //Report Details - End

            _html += "</tr>";
            _html += "</table>";

            _html += "<table width='20%' cellpadding='1' cellspacing='1'>";
            _html += "<tr><td>&nbsp;</td></tr>";
            _html += "<tr><td>&nbsp;</td></tr>";
            _html += "</table>";

            //Agent submission - Start
            _html += "<table width='100%' cellpadding='1' cellspacing='1' border='1'>";
            _html += "<tr><td align='left   ' colspan='9' style='font-size:20px; background-color:#D8D8D8;'><b>" + Resources.General.TransactionDetails + "</b></td></tr>";
            _html += "<tr>";
            _html += "<td width='120' align='left' style='font-size:14px; background-color:#81BEF7;'><b>" + Resources.General.RptApplicationDate + "</b></td>";
            _html += "<td width='100' align='left' style='font-size:14px; background-color:#81BEF7;'><b>" + Resources.General.RptQuoteRefNo + "</b></td>";
            _html += "<td width='200' align='left' style='font-size:14px; background-color:#81BEF7;'><b>" + Resources.General.Broker + "</b></td>";
            _html += "<td width='200' align='left' style='font-size:14px; background-color:#81BEF7;'><b>" + Resources.General.InsuranceType + "</b></td>";
            _html += "<td width='200' align='left' style='font-size:14px; background-color:#81BEF7;'><b>" + Resources.General.InsuranceCompany + "</b></td>";
            _html += "<td width='100' align='right' style='font-size:14px; background-color:#81BEF7;'><b>" + Resources.General.RptVMI + "</b></td>";
            _html += "<td width='100' align='right' style='font-size:14px; background-color:#81BEF7;'><b>" + Resources.General.RptCMI + "</b></td>";
            _html += "<td width='130' align='right' style='font-size:14px; background-color:#81BEF7;'><b>" + Resources.General.TotalPremium + "</b></td>";
            _html += "<td width='130' align='right' style='font-size:14px; background-color:#81BEF7;'><b>" + Resources.General.CollectedAmount + "</b></td>";
            _html += "</tr>";
            for (int j = 0; j < _dsSummary.Tables[0].Rows.Count; j++)
            {

                _html += "<tr>";
                _html += "<td width='120' align='left' style='background-color:#FBEFFB; mso-number-format:dd\\-mmm\\-yyyy hh\\:mm tt'>" + ApplicationDate[j].ToString() + "</td>";
                _html += "<td width='100' align='left' style='background-color:#FBEFFB;'>" + QuoteRefNo[j].ToString() + "</td>";
                _html += "<td width='200' align='left' style='background-color:#FBEFFB;'>" + AgentName[j].ToString() + "</td>";
                _html += "<td width='200' align='left' style='background-color:#FBEFFB;'>" + InsuranceType[j].ToString() + "</td>";
                _html += "<td width='200' align='left' style='background-color:#FBEFFB;'>" + CompanyName[j].ToString() + "</td>";
                _html += "<td width='100' align='right' style='background-color:#FBEFFB; mso-number-format:\\#\\,\\#\\#0\\.00'>" + VMI[j].ToString() + "</td>";
                _html += "<td width='100' align='right' style='background-color:#FBEFFB; mso-number-format:\\#\\,\\#\\#0\\.00'>" + CMI[j].ToString() + "</td>";
                _html += "<td width='130' align='right' style='background-color:#FBEFFB; mso-number-format:\\#\\,\\#\\#0\\.00'>" + Premium[j].ToString() + "</td>";
                _html += "<td width='130' align='right' style='background-color:#FBEFFB; mso-number-format:\\#\\,\\#\\#0\\.00'>" + CollectedAmount[j].ToString() + "</td>";
                _html += "</tr>";

            }
            _html += "<tr>";
            _html += "<td colspan='8' align='right' style='font-size:17px; background-color:#F5FBEF;'><b>" + Resources.General.TotalCollectedAmount + "</b></td>";
            _html += "<td width='100px' align='right' style='font-size:17px; background-color:#F5FBEF; mso-number-format:\\#\\,\\#\\#0\\.00'><b>" + TotalCollectedPremium.ToString() + "</b></td>";
            _html += "</tr>";
            _html += "</table>";
            _html += "</body>";
            _html += "</html>";
            //Agent submission - End

            lblContent.Text = _html;
            ExportGridView(_fileName, lblContent);
        }

    }

    public void ExportGridView(string fileName, Label lblContent)
    {
        string attachment = "attachment; filename=" + fileName + ".xls";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/ms-excel";
        Response.Write(lblContent.Text);
        Response.End();
    }

    private void LoadAgentList()
    {
        try
        {
            MyDatabase _db = new MyDatabase();
            DataTable _dt = new DataTable("tblList");
            string _spName = "sprocCXCRMGetAgentList";

            _dt = _db.GetDataTable(_spName);
            ddlAgentList.Items.Clear();
            if (_dt != null && _dt.Rows.Count > 0)
            {
                ddlAgentList.DataSource = _dt;
                ddlAgentList.DataTextField = "FULLNAME";
                ddlAgentList.DataValueField = "CEID";
                ddlAgentList.DataBind();
                ddlAgentList.Items.Add(new ListItem("Select", "0"));
            }
        }
        catch (Exception Ex)
        {
        }
    }

    public void GenerateExcelTwoDataTableFormate()
    {
        string _html = "";
        string _fileName = "AgentCommision@" + dateTimeNow.Day.ToString("00") + "-" + dateTimeNow.Month.ToString("00") + "-" + dateTimeNow.Year;
        lblContent.Text = "";
        lblError.Text = "";
        if (Validatioin() == true)
        {
            txtReportDate1.Text = "";
            txtReportDate2.Text = dateTimeNow.Year.ToString("0000") + dateTimeNow.Month.ToString("00") + dateTimeNow.Day.ToString("00");
            txtReportDate1.Text = ddlYear1.Text;
            txtReportDate1.Text += ddlMonth1.Text;
            if (ddlReportType.SelectedIndex > 0)
            {
                if (ddlDay1.SelectedIndex >= 0)
                {
                    txtReportDate1.Text += ddlDay1.Text;
                }
            }
            if (ddlReportType.SelectedIndex == 2)
            {
                txtReportDate2.Text = ddlYear2.Text;
                txtReportDate2.Text += ddlMonth2.Text;
                txtReportDate2.Text += ddlDay2.Text;
            }

            Int32 OnlineCount = 0;
            Int32 OfflineCount = 0;
            Int32 TotalFileCount = 0;
            DataSet _dsSummary = new DataSet();
            DataTable _dtSummary = new DataTable();
            Int16 _ReportType = Convert.ToInt16(ddlReportType.Text);
            string _ReportDate1 = txtReportDate1.Text.Trim();
            string _ReportDate2 = txtReportDate2.Text.Trim();
            Int64 AgentId = Convert.ToInt64(ddlAgentList.Text);

            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocCXCRMCompletedApplicationUserSelectedList]";
            SortedList _sPara = new SortedList();


            _sPara.Add("@AgentId", AgentId);
            _sPara.Add("@ReportType", _ReportType);
            _sPara.Add("@ReportDate1", _ReportDate1);
            _sPara.Add("@ReportDate2", _ReportDate2);

            _dsSummary = _db.GetDataSet(_spName, _sPara);
            if (_dsSummary != null && _dsSummary.Tables.Count > 0 && _dsSummary.Tables[0].Rows.Count > 0)
            {
                OnlineCount += _dsSummary.Tables[0].Rows.Count;
            }
            if (_dsSummary != null && _dsSummary.Tables.Count > 1 && _dsSummary.Tables[1].Rows.Count > 0)
            {
                OfflineCount += _dsSummary.Tables[1].Rows.Count;
            }
            TotalFileCount = OnlineCount + OfflineCount;
            if (TotalFileCount > 0)
            {
                string[] AppStatus = new string[TotalFileCount];
                string[] ApplicationDate = new string[TotalFileCount];
                string[] QuoteRefNo = new string[TotalFileCount];
                string[] AgentName = new string[TotalFileCount];
                string[] CustomerName = new string[TotalFileCount];
                string[] Premium = new string[TotalFileCount];
                string[] PaymentType = new string[TotalFileCount];
                float TotalPremium = 0.0f;

                string SCName, SCBank, CardNo, CardType, CardExpiry, Status, VMI, CMI, Commision, TotalCommision;
                SCName = ""; SCBank = ""; CardNo = ""; CardType = ""; CardExpiry = ""; Status = ""; VMI = ""; CMI = ""; Commision = ""; TotalCommision = "";
                if (OnlineCount > 0)
                {
                    for (int i = 0; i < OnlineCount; i++)
                    {
                        AppStatus[i] = _dsSummary.Tables[0].Rows[i]["AppStatus"].ToString();
                        ApplicationDate[i] = Convert.ToDateTime(_dsSummary.Tables[0].Rows[i]["ApplicationDate"].ToString()).ToString("dd/MM/yyyy hh:mm tt");
                        QuoteRefNo[i] = _dsSummary.Tables[0].Rows[i]["QuoteRefNo"].ToString();
                        AgentName[i] = _dsSummary.Tables[0].Rows[i]["AgentName"].ToString();
                        CustomerName[i] = _dsSummary.Tables[0].Rows[i]["CustomerName"].ToString();
                        PaymentType[i] = _dsSummary.Tables[0].Rows[i]["PaymentType"].ToString();
                        Premium[i] = _dsSummary.Tables[0].Rows[i]["AnnualPremium"].ToString();
                        TotalPremium += Convert.ToSingle(Premium[i].ToString());
                    }
                }
                if (OfflineCount > 0)
                {
                    for (int j = OnlineCount; j < TotalFileCount; j++)
                    {
                        Int32 a = 0;
                        AppStatus[j] = _dsSummary.Tables[1].Rows[a]["AppStatus"].ToString();
                        ApplicationDate[j] = Convert.ToDateTime(_dsSummary.Tables[1].Rows[a]["ApplicationDate"].ToString()).ToString("dd/MM/yyyy hh:mm tt");
                        QuoteRefNo[j] = _dsSummary.Tables[1].Rows[a]["QuoteRefNo"].ToString();
                        AgentName[j] = _dsSummary.Tables[1].Rows[a]["AgentName"].ToString();
                        CustomerName[j] = _dsSummary.Tables[1].Rows[a]["CustomerName"].ToString();
                        PaymentType[j] = _dsSummary.Tables[1].Rows[a]["PaymentType"].ToString();
                        Premium[j] = _dsSummary.Tables[1].Rows[a]["AnnualPremium"].ToString();
                        TotalPremium += Convert.ToSingle(Premium[j].ToString());
                        a++;
                    }
                }

                _html += "<html><body><table width='60%' cellpadding='1' cellspacing='1' border='1'>";
                _html += "<tr><td align='left' colspan='15'><b>Agent Commision Details</b></td></tr>";
                _html += "<tr>";
                _html += "<td width='120px' align='left' style='background-color:#FAAC58;'><b>ApplicationStatus</b></td>";
                _html += "<td width='120px' align='left' style='background-color:#FAAC58;'><b>ApplicationDate</b></td>";
                _html += "<td width='100px' align='left' style='background-color:#FAAC58;'><b>QuoteRefNo</b></td>";
                _html += "<td width='200px' align='left' style='background-color:#FAAC58;'><b>AgentName</b></td>";
                _html += "<td width='200px' align='left' style='background-color:#FAAC58;'><b>CustomerName</b></td>";
                _html += "<td width='80px' align='left' style='background-color:#FAAC58;'><b>SCName</b></td>";
                _html += "<td width='80px' align='left' style='background-color:#FAAC58;'><b>SCBank</b></td>";
                _html += "<td width='120px' align='left' style='background-color:#FAAC58;'><b>CardNo</b></td>";
                _html += "<td width='80px' align='left' style='background-color:#FAAC58;'><b>CardType</b></td>";
                _html += "<td width='80px' align='left' style='background-color:#FAAC58;'><b>CardExpiry</b></td>";
                _html += "<td width='50px' align='left' style='background-color:#FAAC58;'><b>Status</b></td>";
                _html += "<td width='100px' align='left' style='background-color:#FAAC58;'><b>VMI</b></td>";
                _html += "<td width='100px' align='left' style='background-color:#FAAC58;'><b>CMI</b></td>";
                _html += "<td width='80px' align='left' style='background-color:#FAAC58;'><b>PaymentType</b></td>";
                _html += "<td width='130px' align='right' style='background-color:#FAAC58;'><b>Premium</b></td>";
                _html += "</tr>";
                for (int j = 0; j < _dsSummary.Tables[0].Rows.Count; j++)
                {
                    _html += "<tr>";
                    _html += "<td width='100px' align='left' style='background-color:#F7F2E0;'>" + AppStatus[j].ToString() + "</td>";
                    _html += "<td width='120px' align='left' style='background-color:#F7F2E0; mso-number-format:dd\\-mmm\\-yyyy hh\\:mm tt'>" + ApplicationDate[j].ToString() + "</td>";
                    _html += "<td width='100px' align='left' style='background-color:#F7F2E0;'>" + QuoteRefNo[j].ToString() + "</td>";
                    _html += "<td width='200px' align='left' style='background-color:#F7F2E0;'>" + AgentName[j].ToString() + "</td>";
                    _html += "<td width='200px' align='left' style='background-color:#F7F2E0;'>" + CustomerName[j].ToString() + "</td>";
                    _html += "<td width='80px' align='left' style='background-color:#F7F2E0;'>" + SCName.ToString() + "</td>";
                    _html += "<td width='80px' align='left' style='background-color:#F7F2E0;'>" + SCBank.ToString() + "</td>";
                    _html += "<td width='120px' align='left' style='background-color:#F7F2E0;'>" + CardNo.ToString() + "</td>";
                    _html += "<td width='80px' align='left' style='background-color:#F7F2E0;'>" + CardType.ToString() + "</td>";
                    _html += "<td width='80px' align='left' style='background-color:#F7F2E0;'>" + CardExpiry.ToString() + "</td>";
                    _html += "<td width='50px' align='left' style='background-color:#F7F2E0;'>" + Status.ToString() + "</td>";
                    _html += "<td width='100px' align='left' style='background-color:#F7F2E0;'>" + VMI.ToString() + "</td>";
                    _html += "<td width='100px' align='left' style='background-color:#F7F2E0;'>" + CMI.ToString() + "</td>";
                    _html += "<td width='80px' align='left' style='background-color:#F7F2E0;'>" + PaymentType[j].ToString() + "</td>";
                    _html += "<td width='130px' align='right' style='background-color:#F7F2E0; mso-number-format:\\#\\,\\#\\#0\\.00'>" + Premium[j].ToString() + "</td>";
                    _html += "</tr>";
                }
                for (int j = 0; j < _dsSummary.Tables[1].Rows.Count; j++)
                {
                    int b = OnlineCount;
                    _html += "<tr>";
                    _html += "<td width='100px' align='left' style='background-color:#F5DA81;'>" + AppStatus[b].ToString() + "</td>";
                    _html += "<td width='120px' align='left' style='background-color:#F5DA81; mso-number-format:dd\\-mmm\\-yyyy hh\\:mm tt'>" + ApplicationDate[b].ToString() + "</td>";
                    _html += "<td width='100px' align='left' style='background-color:#F5DA81;'>" + QuoteRefNo[b].ToString() + "</td>";
                    _html += "<td width='200px' align='left' style='background-color:#F5DA81;'>" + AgentName[b].ToString() + "</td>";
                    _html += "<td width='200px' align='left' style='background-color:#F5DA81;'>" + CustomerName[b].ToString() + "</td>";
                    _html += "<td width='80px' align='left' style='background-color:#F5DA81;'>" + SCName.ToString() + "</td>";
                    _html += "<td width='80px' align='left' style='background-color:#F5DA81;'>" + SCBank.ToString() + "</td>";
                    _html += "<td width='120px' align='left' style='background-color:#F5DA81;'>" + CardNo.ToString() + "</td>";
                    _html += "<td width='80px' align='left' style='background-color:#F5DA81;'>" + CardType.ToString() + "</td>";
                    _html += "<td width='80px' align='left' style='background-color:#F5DA81;'>" + CardExpiry.ToString() + "</td>";
                    _html += "<td width='50px' align='left' style='background-color:#F5DA81;'>" + Status.ToString() + "</td>";
                    _html += "<td width='100px' align='left' style='background-color:#F5DA81;'>" + VMI.ToString() + "</td>";
                    _html += "<td width='100px' align='left' style='background-color:#F5DA81;'>" + CMI.ToString() + "</td>";
                    _html += "<td width='80px' align='left' style='background-color:#F5DA81;'>" + PaymentType[b].ToString() + "</td>";
                    _html += "<td width='130px' align='right' style='background-color:#F5DA81; mso-number-format:\\#\\,\\#\\#0\\.00'>" + Premium[b].ToString() + "</td>";
                    _html += "</tr>";
                    b++;
                }

                _html += "</table></body></html>";

                lblContent.Text = _html;
                ExportGridView(_fileName, lblContent);
            }
        }
        else
        {
        }
    }

    public DataTable GetPercentage(string PremiumLevel, string ReportDate)
    {
        MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
        SortedList _sPara = new SortedList();
        string _spName = "sprocCXCRMAgentCommissionSelectSingleItem";
        _sPara.Add("@PremiumLevel", PremiumLevel);
        _sPara.Add("@ReportDate", ReportDate);
        DataTable _dtPercentage = new DataTable();
        _dtPercentage = _db.GetDataTableBySP(_spName, _sPara);
        return _dtPercentage;
    }

    public string GetReportMonth(string ReportMonth)
    {
        string month = ReportMonth.Substring(4, 2);
        string year = ReportMonth.Substring(0, 4);
        string result = "";
        if (month == "01")
        {
            result = " Jan ";
        }
        else if (month == "02")
        {
            result = " Feb ";
        }
        else if (month == "03")
        {
            result = " Mar ";
        }
        else if (month == "04")
        {
            result = " Apr ";
        }
        else if (month == "05")
        {
            result = " May ";
        }
        else if (month == "06")
        {
            result = " Jun ";
        }
        else if (month == "07")
        {
            result = " Jul ";
        }
        else if (month == "08")
        {
            result = " Aug ";
        }
        else if (month == "09")
        {
            result = " Sep ";
        }
        else if (month == "10")
        {
            result = " Oct ";
        }
        else if (month == "11")
        {
            result = " Nov ";
        }
        else if (month == "12")
        {
            result = " Dec ";
        }
        return result + year;
    }

}
